﻿using System;
using System.Linq;
using System.Data;
using System.Collections.Generic;


namespace SquidConsult.DAL
{
    public class ConsultantDS : PeopleDS
    {
        #region Add Report 
        /// <summary>
        /// Add new expense report to database 
        /// </summary>
        /// <param name="reportId"></param>
        /// <param name="expenseId"></param>
        /// <param name="expenseDate"></param>
        /// <param name="location"></param>
        /// <param name="description"></param>
        /// <param name="expense"></param>
        /// <param name="receiptPath"></param>
        public void SetExpense(Guid reportId, Guid expenseId, System.DateTime expenseDate, string location, string description, decimal expense, string receiptPath)
        {
            expense exp = new expense();
            exp.ReportID = reportId;
            exp.ExpenseID = expenseId;
            exp.ExpenseDate = expenseDate;
            exp.Location = location;
            exp.Expense = expense;
            exp.Description = description;
            exp.ReceiptPath = receiptPath;

            context.AddToexpenses(exp);
            context.SaveChanges();

        }

        public void SetReport(Guid reportId, Guid consultantId, System.DateTime reportDate , decimal totalExpense, string superviorName)
        {
            report rep = new report();

            rep.ReportId = reportId;
            rep.ConsultantId = consultantId;
            rep.ReportDate = reportDate;
            rep.TotalExpense = totalExpense;
            rep.Status_Supervisor = "OnGoing";
            rep.Status_Account = "OnGoing";
            rep.SupervisorName = superviorName;

            context.AddToreports(rep);
            context.SaveChanges();
        }
       #endregion 

        #region Get reports
        /// <summary>
        /// Get All sumitted reports
        /// Get Approved reports
        /// Reports submitted but not yet approved
        /// </summary>
        /// <param name="consultantId"></param>
        /// <returns></returns>
        public IQueryable GetAllReport(Guid consultantId)
        {
            var reports = from rep in context.reports
                          where rep.ConsultantId == consultantId
                          orderby rep.ReportDate
                          select new
                          {
                              rep.ReportId,
                              rep.ReportDate,
                              rep.TotalExpense,
                              rep.Status_Supervisor,
                              rep.Status_Account
                          };
            return reports;
        }

        public IQueryable GetStatusReport(Guid consultanId, string status)
        {
            var reports = from rep in context.reports
                          where rep.ConsultantId == consultanId && rep.Status_Account == status
                          orderby rep.ReportDate
                          select new
                          {
                              rep.ReportDate,
                              rep.TotalExpense,
                              rep.Status_Supervisor,
                              rep.Status_Account
                          };
            return reports;
        }

        //get department supervisor Name
        public IQueryable GetSupervisorName(Guid consultantId)
        {
            //get consultant departmentname
            var consultantDep = context.consultants.Single(c => c.ConsultantId == consultantId);

            var supervisors = from sup in context.supervisors
                              where sup.Department == consultantDep.DepartmentName
                              select sup.SupervisorName;

            return supervisors;
        }
        #endregion 
    }
} 
